package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import book.bookInterface;

import dataPO.BookPO;
import databaseInterface.BookManageInterface;

public class BookManage implements BookManageInterface{
	
	Statement stmt;
	DatabaseConnection db= new DatabaseConnection();
	

    
	public void connect (){       

         stmt =db.connect();
    }
    

	
    public void insert (BookPO b){
    	String insert ="insert into book values ('"+b.getIsbn()+"','"+b.getAuthor()+"','"+b.getTitle()+"','"+b.getPress()+"','"+b.getPubishingTime()+"','"+b.getCat()+"',"+b.getPrice()+","+b.getNum()+")";
        try {
        	stmt.executeUpdate(insert);
		}catch (SQLException e) {

			// TODO Auto-generated catch block
			e.printStackTrace();
		}    
    }


    
	public void delete (String isbn ){   	

    	String delete="delete from book where ISBN= '"+isbn+"'" ;
    	try {
			stmt.executeUpdate(delete);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    
	
    public void update(BookPO b,String isbn){  	
    	String update ="update book set ISBN ='"+b.getIsbn()+"', author = '"+b.getAuthor()+"', title = '"+b.getTitle()+"', press = '"+b.getPress()+"', publishing time = '"+b.getPubishingTime()+"',catalog = '"+b.getCat()+"', price  = "+b.getPrice()+", num = "+b.getNum()+" where ISBN = '"+isbn+"'" ;

        try {
			stmt.executeUpdate(update);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}    
    }
 

    public  ArrayList<BookPO> query ( ){
    	String query = "select * from book ";
        ArrayList<BookPO>  b= query(query);
        return b;
  }
    public ArrayList<BookPO> queryByISBN (String isbn ){
    	String query = "select * from book where isbn = '"+isbn+"'";
        ArrayList<BookPO>  b= query(query);
        return b;
  }
 
    
    public ArrayList<BookPO> queryByTitle  (String t ){
    	String query = "select * from book where title like '%"+t+"%'";
        ArrayList<BookPO>  b= query(query);
        return b;

  }

    
    public ArrayList<BookPO> queryByPress (String p ){

    	String query = "select * from book where press = '"+p+"'";
        ArrayList<BookPO>  b= query(query);
        return b;
  }
  
    public ArrayList<BookPO> queryByAuthor (String a ){

    	String query = "select * from book where author = '"+a+"'";
        ArrayList<BookPO>  b= query(query);
        return b;
  }
 
  
    public ArrayList<BookPO> queryByCatalog (String cat ){
    	String query = "select * from book where catalog = '"+cat+"'";
        ArrayList<BookPO>  b= query(query);
        return b;
    }
  


    
    private ArrayList<BookPO> query (String query ){
    	ArrayList<BookPO> booklist = new ArrayList<BookPO> ();
      	try {
			ResultSet rs= stmt.executeQuery(query);
			while(rs.next()){
				BookPO b= toBookPO(rs);
				booklist.add(b);
			}
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return booklist;	
    }
    
    private BookPO toBookPO(ResultSet rs) {
	// TODO Auto-generated method stub
    	BookPO b = new BookPO();
    	try {
    		b.setIsbn(rs.getString(1));
    		b.setAuthor( rs.getString(2));
    		b.setTitle(rs.getString(3));
    		b.setPress(rs.getString(4));
    		b.setPublishedTime( rs.getString(5));
    		b.setCat(rs.getString(6));
    		b.setPrice( rs.getDouble(7));
    		b.setNum(rs.getInt(8));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
    	return b;
    }
    

	public void close(){      
		db.close();

    }
   
}